package sql;
import guiDisplay.UserData2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;
import java.util.TreeSet;
import java.util.Vector;

import data.*;

public class SQLUtilityManagement {

	private static String _userMask = "INSERT INTO users(name, password, bdate, sex, type) VALUES(";
	private static String _moviesMask = "INSERT INTO movies(name, category, year, director, picture, rating, raters, language, length) VALUES(";
	private static String _ratingMask = "INSERT INTO ratings(user, movie, rating, date, description) VALUES(";

//	static {
//		try {
//			Class.forName("com.mysql.jdbc.Driver");
//		} catch (ClassNotFoundException ex) {
//			ex.printStackTrace();
//		}
//	}

	public static void init() {
		/*executeQuery("use movies_db");
		// TODO Auto-generated method stub
		String[] movies = getMovieNames();
		for (int i=0; i< movies.length; i++){
			Set<RatingTuple> ratings = getRatingsByMovie(movies[i]);
			double raters = ratings.size();
			double totalRating =0;
			for (RatingTuple r: ratings){
				totalRating+=r.get_rating();
			}
			if (raters!=0){
				String cmd = "UPDATE movies SET rating="+(totalRating/raters)+" , raters="+raters+" WHERE name='"+movies[i]+"';";
				executeUpdate(cmd);
			}
		}*/
	}

	public static UserTuple getUser(String user){
		String cmd = "SELECT * FROM users WHERE name='"+user+"';";
		
		ResultSet r = executeQuery(cmd);

		try {
			if(r==null || !r.first()){
				
				return null;
			}else{
				return new UserTuple(r.getString("name"),r.getString("password"),r.getString("sex"),r.getString("bdate"),r.getString("type"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static MovieTuple getMovie(String movie){
		String cmd = "SELECT * FROM movies WHERE name='"+movie+"';";
		ResultSet r = executeQuery(cmd);
		try {
			if(!r.next()){
				
				return null;
			}else{
				return new MovieTuple(r.getString("name"),r.getString("category"), r.getString("director"),r.getString("language"),r.getString("raters"),r.getString("rating"),r.getString("year"),r.getString("length"),r.getString("picture"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static TreeSet<RatingTuple> getRatingsByUser(String user){
		String cmd = "SELECT * FROM ratings WHERE user='"+user+"';";
		ResultSet r = executeQuery(cmd);
		TreeSet<RatingTuple> ans = new TreeSet<RatingTuple>(new CompartorMovie());
		try {
			while(r.next()){
				ans.add(RatingTuple.createNew(r.getString("movie"), user, r.getString("date"), r.getString("rating"), r.getString("description")));
			}
			return ans;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

	}

	public static TreeSet<RatingTuple> getRatingsByMovie(String movie){
		String cmd = "SELECT * FROM ratings WHERE movie='"+movie+"';";
		ResultSet r = executeQuery(cmd);
		TreeSet<RatingTuple> ans = new TreeSet<RatingTuple>(new CompartorUser());
		try {
			while(r.next()){
				ans.add(RatingTuple.createNew(movie,r.getString("user"), r.getString("date"), r.getString("rating"), r.getString("description")));
			}
			return ans;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

	}

	public static RatingTuple getRating(String movie, String user){
		String cmd = "SELECT * FROM ratings WHERE user='"+user+"' AND movie='"+movie+"';";
		ResultSet r = executeQuery(cmd);
		try {
			if(!r.next()){
				//new Exception().printStackTrace();
				return null;
			}else{
				return RatingTuple.createNew(movie, user, r.getString("date"), r.getString("rating"), r.getString("description"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static void updateUserDetails(String name, String bdate, String sex){
		String cmd = "UPDATE users SET bdate='"+bdate+"' , sex='"+sex+"' WHERE name='"+name+"';";
		executeUpdate(cmd);
	}

	public static void updateUserPassword(String name, String password){
		String cmd = "UPDATE users SET password='"+password+"' WHERE name='"+name+"';";
		executeUpdate(cmd);
	}

//	public static void setMovie(MovieTuple movie){
//		String cmd = "UPDATE movies SET bdate='"+bdate+"' , sex='"+sex+"' WHERE name='"+movie.get_name()+"';";
//		executeUpdate(cmd);
//	}

	public static boolean addUser(UserTuple user){
		String cmd = _userMask + "'"+ user.get_name() +"', '"+
		  							  user.get_password() + "', '" +
		  							  user.get_bdate() + "', '" +
		  							  user.is_sex()+"', '"+
		  							  user.get_type() +"');";
		return executeUpdate(cmd);
	}

	public static void addMovie(MovieTuple movie){
		String rate = movie.get_rating()+"";
		if(rate.length()>5)
			rate=rate.substring(0, 4);
		String cmd = _moviesMask + "'"+ movie.get_name() +"', '"+
		  								movie.get_category() + "', '" +
		  								movie.get_year() + "', '" +
		  								movie.get_director() +"', '"+
		  								movie.get_picture() + "', '" +
		  							/*	movie.get_raters()*/rate  +"', '"+
		  							movie.get_raters()+ "', '" +
		  								movie.get_language() +"', '"+
		  								movie.get_length() +"');";
		executeUpdate(cmd);
	}

	public static void setRating(RatingTuple rec){
		String cmd = "UPDATE ratings SET rating='"+rec.get_rating()+"' ,"+
		                               " description='"+rec.get_description()+"' ,"+
		                               " date='"+rec.get_date()+"' " +
		                               " WHERE user='"+rec.get_user().get_name()+"' AND movie='"+rec.get_movie().get_name()+"';";
		executeUpdate(cmd);
	}
	public static void setRating(String user , String movie , String rating , String description , String date){
		String cmd = "UPDATE ratings SET rating='"+rating + "' ,"+
		                               " description='"+description+"' ,"+
		                               " date='"+date+"' " +
		                               " WHERE user='"+user+"' AND movie='"+movie+"';";
		executeUpdate(cmd);
	}

	public static void addRating(String user, String movie , String rating , String description,String date){
		String cmd = _ratingMask + "'"+ user +"', '"+
										movie + "', '" +
										rating + "', '" +
										date + "', '" +
										description +"');";
		executeUpdate(cmd);

	}

	public static void addRating(RatingTuple rec){
		String cmd = _ratingMask + "'"+ rec.get_user().get_name() +"', '"+
										rec.get_movie().get_name() + "', '" +
										rec.get_rating() + "', '" +
										rec.get_date() + "', '" +
										rec.get_description() +"');";
		executeUpdate(cmd);

	}

	public static void updateMovieRating(String movie, String newRate) {
		if(newRate.length()>5)
			newRate=newRate.substring(0, 5);
		String cmd = "UPDATE movies SET rating='"+newRate+"' " +
        			 " WHERE name='"+movie+"' ;";
		executeUpdate(cmd);
	}

	public static void updateMovieRating(String movie, String newRate, String raters) {
		if(newRate.length()>5)
			newRate=newRate.substring(0, 5);
		String cmd = "UPDATE movies SET rating='"+newRate+"' ," +
		  							   "raters='"+raters +"' " +
        			 " WHERE name='"+movie+"' ;";
		executeUpdate(cmd);
	}

	public static Set<MovieTuple> getAllMovies() {
		String cmd = "SELECT * FROM movies;";
		ResultSet r = executeQuery(cmd);
		HashSet<MovieTuple> ans = new HashSet<MovieTuple>();
		try {
			while(r.next()){
				ans.add(new MovieTuple(r.getString("name"),r.getString("category"), r.getString("director"),r.getString("language"),r.getString("raters"),r.getString("rating"),r.getString("year"),r.getString("length"),r.getString("picture")));
			}
			return ans;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static MovieTuple[] getUserMovies(String userName, int minRating){
		//TODO sort
		String cmd = "SELECT movies.* FROM movies, ratings WHERE ratings.movie = movies.name AND ratings.user= '"+userName+"' AND ratings.rating>="+minRating + " ORDER BY movie ";
		ResultSet r = executeQuery(cmd);
		Vector<MovieTuple> ans = new Vector<MovieTuple>();
		try {
			while(r.next()){
				ans.add(new MovieTuple(r.getString("name"),r.getString("category"), r.getString("director"),r.getString("language"),r.getString("raters"),r.getString("rating"),r.getString("year"),r.getString("length"),r.getString("picture")));
			}
			return ans.toArray(new MovieTuple[ans.size()]);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static UserData2[] getMovieRaters(String movieName, int minRating){
		String cmd = "SELECT users.*, ratings.rating FROM users, ratings WHERE ratings.user = users.name AND ratings.movie= '"+movieName+"' AND ratings.rating>="+minRating + " ORDER BY name" ;
		ResultSet r = executeQuery(cmd);
		Vector<UserData2> ans = new Vector<UserData2>();
		
		try {
			while(r.next()){				
				ans.add(new UserData2(r.getString("name"),r.getString("sex"),r.getString("bdate"),r.getInt("rating")));
			}
			return ans.toArray(new UserData2[ans.size()]);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static MovieTuple[] getNMovies(int n, boolean highest){
		//TODO sort
		String cmd = "SELECT * FROM movies ORDER BY rating" + (highest ?  " DESC" : "") ;
		ResultSet r = executeQuery(cmd);
		Vector<MovieTuple> ans = new Vector<MovieTuple>();
		try {
			int i=0;
			while(r.next() && i < n){
				ans.add(new MovieTuple(r.getString("name"),r.getString("category"), r.getString("director"),r.getString("language"),r.getString("raters"),r.getString("rating"),r.getString("year"),r.getString("length"),r.getString("picture")));
				i++;
			}
			MovieTuple[] allMovies = ans.toArray(new MovieTuple[ans.size()]);
			
			return allMovies;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static Set<UserTuple> getAllUsers() {
		String cmd = "SELECT * FROM users;";
		ResultSet r = executeQuery(cmd);
		HashSet<UserTuple> ans = new HashSet<UserTuple>();
		try {
			while(r.next()){
				ans.add(new UserTuple(r.getString("name"),r.getString("password"),r.getString("sex"),r.getString("bdate"),r.getString("type")));
			}
			return ans;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static Set<RatingTuple> getAllRating() {
		String cmd = "SELECT * FROM ratings;";
		ResultSet r = executeQuery(cmd);
		HashSet<RatingTuple> ans = new HashSet<RatingTuple>();
		try {
			while(r.next()){
				ans.add(RatingTuple.createNew(r.getString("movie"),r.getString("user"),r.getString("date"),r.getString("rating"),r.getString("description")));
			}
			return ans;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}


	public static String[] getMovieNames() {
		String cmd = "SELECT name FROM movies ORDER BY name;";
		ResultSet r = executeQuery(cmd);
		Vector<String> ans = new Vector<String>();
		try {
			while(r.next()){
				ans.add(r.getString("name"));
			}
			return ans.toArray(new String[ans.size()]);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static String[] getUserNames(String name) {
		String cmd = "SELECT name FROM users WHERE name<>'"+name+"' ORDER BY name;";
		ResultSet r = executeQuery(cmd);
		Vector<String> ans = new Vector<String>();
		try {
			while(r.next()){
				ans.add(r.getString("name"));
			}
			return ans.toArray(new String[ans.size()]);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static String[] getAllNames() {
		String cmd = "SELECT name FROM users ORDER BY name;";
		ResultSet r = executeQuery(cmd);
		Vector<String> ans = new Vector<String>();
		try {
			while(r.next()){
				ans.add(r.getString("name"));
			}
			return ans.toArray(new String[ans.size()]);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static String[] getRegUserNames() {
		String cmd = "SELECT name FROM users WHERE type='user' ORDER BY name;";
		ResultSet r = executeQuery(cmd);
		Vector<String> ans = new Vector<String>();
		try {
			while(r.next()){
				ans.add(r.getString("name"));
			}
			return ans.toArray(new String[ans.size()]);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static void deleteMovie(String movie){
		String cmd = "DELETE FROM movies WHERE name='"+movie+"';";
		executeUpdate(cmd);
	}
	public static void deleteUser(String user){

		String cmd = "DELETE FROM users WHERE name='"+user+"';";
		executeUpdate(cmd);
	}
	public static void deleteRating(String user, String movie){
		String cmd = "DELETE FROM ratings WHERE user='"+user+"' AND movie='"+movie+"';";
		executeUpdate(cmd);
	}


	private static boolean executeUpdate(String cmd){

		Statement stmt;
		String url = "jdbc:mysql://localhost:3306/movies_db";

		//Get a connection to the database for a
		// user named root with a blank password.
		// This user is the default administrator
		// having full privileges to do anything.
		Connection con;
		try {
			con = DriverManager.getConnection(url,"admin", "dog");
			stmt = con.createStatement();
			stmt.executeUpdate(cmd);
			con.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	private static ResultSet executeQuery(String cmd){
		Statement stmt;

		String url = "jdbc:mysql://localhost:3306/movies_db";
		//Get a connection to the database for a
		// user named root with a blank password.
		// This user is the default administrator
		// having full privileges to do anything.
		//Connection con;
		try {
			Connection con = DriverManager.getConnection(url,"admin", "dog");
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			//con.close();
			//stmt = con.createStatement();
			return stmt.executeQuery(cmd);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static void setAdmin(String name) {
		String cmd = "UPDATE users SET type='admin' WHERE name='"+name+"' ;";
		executeUpdate(cmd);
	}

	public static void setManager(String name) {
		String cmd = "UPDATE users SET type='manager' WHERE name='"+name+"' ;";
		executeUpdate(cmd);

	}

	public static String[] getNotMngUserNames() {
		String cmd = "SELECT name FROM users WHERE type='user' or type='admin' ORDER BY name;";
		ResultSet r = executeQuery(cmd);
		Vector<String> ans = new Vector<String>();
		try {
			while(r.next()){
				ans.add(r.getString("name"));
			}
			return ans.toArray(new String[ans.size()]);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static double getAvg(String user) {
		String cmd = "SELECT AVG(rating) FROM ratings WHERE user='"+user+"';";
		ResultSet r = executeQuery(cmd);
		try {
			if (r.next())
				return r.getDouble("AVG(rating)");
			else 
				return 0;

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;

	}


	//TODO there are many more optimizations we can use using the SQL engine (especially with the algorithm) , by moving more operations to the SystemData interface

}
